<?php 
namespace lib;

class DB {
    use \lib\DBTraits;//db的辅助方法
    public function __construct(){

    }
     /**
     * 通用查询方法，支持多种查询条件和高级功能
     * @param string $table 表名
     * @param array $conditions 查询条件
     * @param array $options 额外选项
     * @return mixed
     */
    public function query($table, $conditions = [], $options = []) {
        $defaultOptions = [
            'operation' => 'select',    // 操作类型
            'columns' => '*',           // 查询列
            'where' => [],             // WHERE条件
            'orderBy' => '',           // 排序
            'groupBy' => '',           // 分组
            'having' => '',            // HAVING条件
            'limit' => '',             // LIMIT
            'offset' => '',            // OFFSET
            'join' => [],              // JOIN条件
            'single_row' => false,     // 是否返回单行
            'debug' => false           // 调试模式
        ];
        
        $options = array_merge($defaultOptions, $options);
        
        switch ($options['operation']) {
            case 'insert':
                return $this->insert($table, $conditions);
            case 'update':
                return $this->update($table, $conditions, $options['where']);
            case 'delete':
                return $this->delete($table, $conditions);
            case 'select':
            default:
                return $this->select($table, $options['columns'], $conditions, $options);
        }
    }

    /**
     * 多条件插入
     * @param string $table 表名
     * @param array $data 插入数据
     * @return mixed
     */
    public function insert($table, $data) {
        
        // 单条插入
        $table = dbconfig['sqlname'] . $table;
        if (isset($data[0]) && is_array($data[0])) {
            // 批量插入
            $columns = array_keys($data[0]);
            $placeholders = [];
            $allParams = [];
    
            foreach ($data as $index => $row) {
                $rowPlaceholders = [];
                foreach ($columns as $column) {
                    $paramName = $column . '_' . $index;
                    $rowPlaceholders[] = ":$paramName";
                    $allParams[$paramName] = $row[$column];
                }
                $placeholders[] = '(' . implode(', ', $rowPlaceholders) . ')';
            }
    
            $columnStr = implode(', ', array_map(function($column) {
                return "`$column`";
            }, $columns));
            $placeholderStr = implode(', ', $placeholders);
    
            $sql = "INSERT INTO `$table` ($columnStr) VALUES $placeholderStr";
            
            return $this->execute($sql, $allParams, 'insert');
        } else {
            // 单条插入
            $columnkey = array_keys($data);
            $columns = implode(', ', array_map(function($key) {
                return "`$key`";
            }, $columnkey));
            
            $placeholders = ':' . implode(', :', $columnkey);
            
            $sql = "INSERT INTO `$table` ($columns) VALUES ($placeholders)";
            
            return $this->execute($sql, $data, 'insert');
        }
    }

    /**
     * 多条件更新
     * @param string $table 表名
     * @param array $data 更新数据
     * @param array $options 额外选项，包括更新条件和额外的参数
     * @return mixed
     */
    public function update($table, $data, $wheredata = []) {
        $table = dbconfig['sqlname'] . $table;
        // 处理更新数据
        $set = [];
        $params = [];
    
        foreach ($data as $column => $value) {
            $paramName = ":$column";
            $set[] = "`$column` = $paramName";
            $params[$column] = $value;
        }
    
        $setStr = implode(', ', $set);
    
        // 构建WHERE条件
        $whereConditions = $wheredata ?? [];
        
        $where = [];
        foreach ($whereConditions as $column => $value) {
            $paramName = ":$column";
            if (is_array($value)) {
                // 支持 IN 查询
                $inParams = [];
                foreach ($value as $k => $v) {
                    $inParamName = $column . '_' . $k;
                    $inParams[] = ":$inParamName";
                    $params[$inParamName] = $v;
                }
                $where[] = "`$column` IN (" . implode(',', $inParams) . ")";
            } else {
                $where[] = "`$column` = $paramName";
                $params[$column] = $value;
            }
        }
    
        $whereStr = implode(' AND ', $where);
        
        $sql = "UPDATE `$table` SET $setStr";
        
        if ($whereStr) {
            $sql .= " WHERE $whereStr";
        }

        return $this->execute($sql, $params, 'update');
    }

    /**
     * 删除数据库记录
     * @param string $table 表名
     * @param array|null $conditions 条件数组，传null则删除全部
     * @return bool|int 返回受影响的行数
     */
    public function delete($table, $conditions = null) {
        $table = dbconfig['sqlname'] . $table;
        
        // 删除全部内容
        if ($conditions === null) {
            $sql = "DELETE FROM `$table`";
            return $this->execute($sql, [], 'delete');
        }
        
        $where = [];
        $params = [];
    
        foreach ($conditions as $column => $value) {
            if (is_array($value)) {
                // 处理 IN 查询
                $placeholders = [];
                foreach ($value as $k => $v) {
                    $paramName = $column . $k;
                    $placeholders[] = ":{$paramName}";
                    $params[$paramName] = $v;
                }
                $where[] = "`$column` IN (" . implode(',', $placeholders) . ")";
            } else {
                $where[] = "`$column` = :{$column}";
                $params[$column] = $value;
            }
        }
    
        $whereStr = !empty($where) ? " WHERE " . implode(' AND ', $where) : '';
        $sql = "DELETE FROM `$table`{$whereStr}";
    
        return $this->execute($sql, $params, 'delete');
    }
    /**
     * 增强的删除方法
     * @param string $table 表名
     * @param array|null $conditions 条件数组
     * @param array $options 附加选项
     * @return array
     */
    public function complexdelete($table, $conditions = null, $options = []) {
        $prefix = dbconfig['sqlname'];
        $table = $this->formatTableName($table, $prefix);
        
        // 删除全部内容
        if ($conditions === null) {
            $sql = "DELETE FROM `$table`";
            return $this->execute($sql, [], 'delete');
        }

        $whereBuilder = $this->buildWhereClause($conditions);
        $whereStr = $whereBuilder['where'];
        $params = $whereBuilder['params'];

        // 添加 LIMIT 支持
        $limitStr = '';
        if (!empty($options['limit'])) {
            $limitStr = " LIMIT " . intval($options['limit']);
        }

        // 添加 ORDER BY 支持
        $orderStr = '';
        if (!empty($options['order'])) {
            $orderStr = " ORDER BY " . $options['order'];
        }

        $sql = "DELETE FROM `$table`{$whereStr}{$orderStr}{$limitStr}";
        
        // 调试模式
        if (!empty($options['debug'])) {
            return ['sql' => $sql, 'params' => $params];
        }

        return $this->execute($sql, $params, 'delete');
    }

    /**
     * 构建 WHERE 子句
     * @param array $conditions 条件数组
     * @return array 包含 where 子句和参数的数组
     */
    private function buildWhereClause($conditions) {
        $where = [];
        $params = [];
        $groupStack = [];
        
        foreach ($conditions as $key => $condition) {
            // 处理 OR 分组
            if ($key === 'OR') {
                $orGroup = [];
                foreach ($condition as $orCondition) {
                    $subWhere = $this->buildWhereClause($orCondition);
                    $orGroup[] = '(' . trim($subWhere['where'], ' WHERE') . ')';
                    $params = array_merge($params, $subWhere['params']);
                }
                $where[] = '(' . implode(' OR ', $orGroup) . ')';
                continue;
            }

            // 处理普通条件
            if (is_array($condition)) {
                $operator = $condition[0] ?? '=';
                $value = $condition[1] ?? null;
                
                // 生成唯一的参数名
                $paramName = str_replace('.', '_', $key) . '_' . count($params);

                switch (strtoupper($operator)) {
                    case 'IN':
                        $placeholders = [];
                        foreach ($value as $k => $v) {
                            $inParamName = $paramName . '_' . $k;
                            $placeholders[] = ":{$inParamName}";
                            $params[$inParamName] = $v;
                        }
                        $where[] = "`$key` IN (" . implode(',', $placeholders) . ")";
                        break;
                        
                    case 'NOT IN':
                        $placeholders = [];
                        foreach ($value as $k => $v) {
                            $inParamName = $paramName . '_' . $k;
                            $placeholders[] = ":{$inParamName}";
                            $params[$inParamName] = $v;
                        }
                        $where[] = "`$key` NOT IN (" . implode(',', $placeholders) . ")";
                        break;

                    case 'LIKE':
                    case 'NOT LIKE':
                        $where[] = "`$key` $operator :{$paramName}";
                        $params[$paramName] = $value;
                        break;

                    case 'BETWEEN':
                        $where[] = "`$key` BETWEEN :{$paramName}_1 AND :{$paramName}_2";
                        $params[$paramName . '_1'] = $value[0];
                        $params[$paramName . '_2'] = $value[1];
                        break;

                    case 'IS NULL':
                    case 'IS NOT NULL':
                        $where[] = "`$key` $operator";
                        break;

                    default:
                        $where[] = "`$key` $operator :{$paramName}";
                        $params[$paramName] = $value;
                }
            } else {
                // 简单等于条件
                $paramName = str_replace('.', '_', $key);
                $where[] = "`$key` = :{$paramName}";
                $params[$paramName] = $condition;
            }
        }

        $whereStr = !empty($where) ? " WHERE " . implode(' AND ', $where) : '';
        return ['where' => $whereStr, 'params' => $params];
    }
    /**
     * 批量删除记录
     * @param string $table 表名
     * @param array $ids ID数组
     * @param string $idField ID字段名，默认为'id'
     * @return bool|int
     */
    public function batchDelete($table, array $ids, $idField = 'id') {
        if (empty($ids)) {
            return false;
        }
        return $this->delete($table, [$idField => $ids]);
    }
    /**
     * 高级查询构建器
     * @param string $table 表名
     * @param string|array $columns 查询列
     * @param array $conditions 查询条件
     * @param array $options 查询选项
     * @return mixed
     */
     //$this->db->select("user",'*',['pid'=>'181423595'],['single_row'=>true,'limit'=>1])['result']
    public function select($table, $columns, $conditions, $options = []) {
        $table = dbconfig['sqlname'] . $table;
        $params = [];
        $joins = [];
        $where = [];
        
        // 处理JOIN
        if (!empty($options['join'])) {
            foreach ($options['join'] as $join) {
                $joins[] = "{$join['type']} JOIN {$join['table']} ON {$join['on']}";
            }
        }
        
        // 处理WHERE条件
        foreach ($conditions as $column => $value) {
            if (is_array($value)) {
                switch ($value['operator'] ?? 'eq') {
                    case 'in':
                        $placeholders = [];
                        foreach ($value['value'] as $k => $v) {
                            $paramName = "{$column}_{$k}";
                            $placeholders[] = ":{$paramName}";
                            $params[$paramName] = $v;
                        }
                        $where[] = "`$column` IN (" . implode(',', $placeholders) . ")";
                        break;
                    case 'notin':
                        $placeholders = [];
                        foreach ($value['value'] as $k => $v) {
                            $paramName = "{$column}_{$k}";
                            $placeholders[] = ":{$paramName}";
                            $params[$paramName] = $v;
                        }
                        $where[] = "`$column` NOT IN (" . implode(',', $placeholders) . ")";
                        break;
                    case 'between':
                        $where[] = "`$column` BETWEEN :{$column}_start AND :{$column}_end";
                        $params["{$column}_start"] = $value['start'];
                        $params["{$column}_end"] = $value['end'];
                        break;
                        
                    case 'like':
                        $where[] = "`$column` LIKE :{$column}";
                        $params[$column] = '%' . $value['value'] . '%';
                        break;
                    case 'gt':
                        $where[] = "`$column` > :$column";
                        $params[$column] = $value['value'];
                        break;
                    case 'lt':
                        $where[] = "`$column` < :$column";
                        $params[$column] = $value['value'];
                        break;
                    case 'null':
                        $where[] = "`$column` IS NULL";
                        break;
                        
                    case 'not_null':
                        $where[] = "`$column` IS NOT NULL";
                        break;
                        
                    default:
                        $where[] = "`$column` {$value['operator']} :{$column}";
                        $params[$column] = $value['value'];
                }
            } else {
                $where[] = "`$column` = :{$column}";
                $params[$column] = $value;
            }
        }
        
        // 构建SQL
        $sql = "SELECT $columns FROM `$table`";
        
        if ($joins) {
            $sql .= ' ' . implode(' ', $joins);
        }
        
        if ($where) {
            $sql .= " WHERE " . implode(' AND ', $where);
        }
        
        if (!empty($options['groupBy'])) {
            $sql .= " GROUP BY {$options['groupBy']}";
            
            if (!empty($options['having'])) {
                $sql .= " HAVING {$options['having']}";
            }
        }
        
        if (!empty($options['orderBy'])) {
            $sql .= " ORDER BY {$options['orderBy']}";
        }
        
        if (!empty($options['limit'])) {
            $sql .= " LIMIT {$options['limit']}";
            if (!empty($options['offset'])) {
                $sql .= " OFFSET {$options['offset']}";
            }
        }
        
        return $this->execute($sql, $params, 'select', $options);
    }
    /**
     * 高级复杂查询构建器
     * @param string $table 主表名
     * @param string|array $columns 查询列
     * @param array $conditions 查询条件
     * @param array $options 高级选项
     * @return mixed
     */
    public function complexSelect($table, $columns, $conditions, $options = []) {
        // 处理主表
        $tableParts = explode(' ', $table);
        $mainTable = dbconfig['sqlname'] . $tableParts[0];
        $mainAlias = $tableParts[1] ?? '';
        $table = $mainTable . ($mainAlias ? ' '.$mainAlias : '');
        
        $params = [];
        $joins = [];
        $where = [];
        
        // 增强的JOIN处理
        if (!empty($options['join'])) {
            foreach ($options['join'] as $join) {
                $joinTable = $join['table'];
                if (strpos($joinTable, 'pre_') === false && strpos($joinTable, dbconfig['sqlname']) === false) {
                    $tableParts = explode(' ', $joinTable);
                    $joinTable = dbconfig['sqlname'] . $tableParts[0] . (isset($tableParts[1]) ? ' '.$tableParts[1] : '');
                }
                $joins[] = "{$join['type']} JOIN {$joinTable} ON {$join['on']}";
            }
        }
        
        // 增强的WHERE条件处理
        foreach ($conditions as $column => $value) {
            // 处理带表别名的列
            $columnName = strpos($column, '.') !== false ? $column : "`$column`";
            
            if (is_array($value)) {
                switch ($value['operator'] ?? 'eq') {
                    case 'raw':
                        // 支持原生SQL条件
                        $where[] = $value['value'];
                        if (!empty($value['params'])) {
                            $params = array_merge($params, $value['params']);
                        }
                        break;
                        
                    case 'in':
                    case 'notin':
                        $placeholders = [];
                        foreach ($value['value'] as $k => $v) {
                            $paramName = str_replace('.', '_', $column) . "_{$k}";
                            $placeholders[] = ":{$paramName}";
                            $params[$paramName] = $v;
                        }
                        $operator = $value['operator'] === 'in' ? 'IN' : 'NOT IN';
                        $where[] = "{$columnName} {$operator} (" . implode(',', $placeholders) . ")";
                        break;
                        
                    case 'between':
                        $columnKey = str_replace('.', '_', $column);
                        $where[] = "{$columnName} BETWEEN :{$columnKey}_start AND :{$columnKey}_end";
                        $params["{$columnKey}_start"] = $value['start'];
                        $params["{$columnKey}_end"] = $value['end'];
                        break;
                        
                    default:
                        if (isset($value['operator']) && isset($value['value'])) {
                            $columnKey = str_replace('.', '_', $column);
                            $where[] = "{$columnName} {$value['operator']} :{$columnKey}";
                            $params[$columnKey] = $value['value'];
                        }
                }
            } else {
                $columnKey = str_replace('.', '_', $column);
                $where[] = "{$columnName} = :{$columnKey}";
                $params[$columnKey] = $value;
            }
        }
        
        // 构建SQL
        $sql = "SELECT $columns FROM $table";
        
        if ($joins) {
            $sql .= ' ' . implode(' ', $joins);
        }
        
        if ($where) {
            $sql .= " WHERE " . implode(' AND ', $where);
        }
        
        // 处理其他选项
        if (!empty($options['groupBy'])) {
            $sql .= " GROUP BY {$options['groupBy']}";
            if (!empty($options['having'])) {
                $sql .= " HAVING {$options['having']}";
            }
        }
        
        if (!empty($options['orderBy'])) {
            $sql .= " ORDER BY {$options['orderBy']}";
        }
        
        if (!empty($options['limit'])) {
            $sql .= " LIMIT {$options['limit']}";
            if (!empty($options['offset'])) {
                $sql .= " OFFSET {$options['offset']}";
            }
        }
        
        return $this->execute($sql, $params, 'select', $options);
    }
    private function execute($sql, $params, $operation, $options = []) {
        return safeSqlQuery($sql, $params, $operation, $options);
    }
    public  function adminupnum($id , $ip , $num = 0 , $send = false){
        $where = $send ? " `login_count`= '{$num}'" : " `login_count` = `login_count` + 1";
        $sql = "SELECT * FROM `".dbconfig['sqlname']."admin_logins` WHERE `ip` = '{$ip}' and `admin_id` = '{$id}' limit 1";
        
        if(executeSQLQuery($sql)){
            $sql = "update `".dbconfig['sqlname']."admin_logins` set {$where} where `ip`='$ip' and `admin_id` = '{$id}' ";
        }else{
            $sql = "REPLACE INTO `".dbconfig['sqlname']."admin_logins` SET `ip`='$ip' ,`login_count`='0', `admin_id`='$id'";
        }
       
        return executeSQLQuery($sql, 'update'); 
    }
    public  function adminnum($id , $ip){
        $sql = "SELECT * FROM `".dbconfig['sqlname']."admin_logins` WHERE `ip` = '{$ip}' and `admin_id` = '{$id}' limit 1";
       
        return executeSQLQuery($sql)['login_count'];
    }
    public  function userupnum($id , $ip , $num = 0 , $send = false){
        $where = ($send) ? " `login_count`= '{$num}'" : " `login_count` = `login_count` + 1";
        $sql = "SELECT * FROM `".dbconfig['sqlname']."admin_logins` WHERE `ip` = '{$ip}' and `user_id` = '{$id}' limit 1";
        
        if(executeSQLQuery($sql)){
            $sql = "update `".dbconfig['sqlname']."admin_logins` set {$where} where `ip`='{$ip}' and `user_id` = '{$id}' ";
        }else{
            $sql = "REPLACE INTO `".dbconfig['sqlname']."admin_logins` SET `ip`='$ip' ,`login_count`='0' , `user_id`='$id'";
        }
        
        return executeSQLQuery($sql, 'update'); 
    }
    public  function usernum($id,$ip){
        $sql = "SELECT * FROM `".dbconfig['sqlname']."admin_logins` WHERE `ip` = '{$ip}' and `user_id` = '{$id}' limit 1";
        return executeSQLQuery($sql)['login_count'];
    }
}


?>